package manager;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import entity.Course;
import entity.Section;

/**
 * An utility class to execute read queries, handle result sets and obtain
 * results in the desired entity object type.
 * 
 * @author
 * 
 */
class RetrieveSectiontUtil {

	/**
	 * Convenient method to retrieve multiple sections from database for the
	 * given query.
	 * 
	 * @param dbMgr
	 *            database manager object on which the query should be executed
	 * @param sql
	 *            the SQL statement to execute
	 * @param param
	 *            sequence of parameters to be used in the SQL statement
	 * @return an ArrayList of section objects with information retrieved from
	 *         the database
	 */
	static ArrayList<Section> retrieveMultipleSectionsForQuery(
			DatabaseMgr dbMgr, String sql, Object... param) {
		ArrayList<Section> sections = new ArrayList<Section>();

		try {
			ResultSet rs = dbMgr.openAndRead(sql, param);
			while (rs.next()) {
				sections.add(newSectionFromResultSet(rs));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbMgr.close();
		}

		return sections;
	}

	/**
	 * Convenient method to retrieve a single section from database for the
	 * given query.
	 * 
	 * @param dbMgr
	 *            database manager object on which the query should be executed
	 * @param sql
	 *            the SQL statement to execute
	 * @param param
	 *            sequence of parameters to be used in the SQL statement
	 * @return a new section object with information retrieved from the database
	 */
	static Section retrieveSectionForQuery(DatabaseMgr dbMgr, String sql,
			Object... param) {
		Section section = null;

		try {
			ResultSet rs = dbMgr.openAndRead(sql, param);
			while (rs.next()) {
				section = newSectionFromResultSet(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbMgr.close();
		}

		return section;
	}

	/**
	 * Convenient method to create a new section object from the given result
	 * set.
	 * 
	 * @param rs
	 *            result set containing section information
	 * @return a new section object with information extracted from the result
	 *         set
	 * @throws SQLException
	 */
	private static Section newSectionFromResultSet(ResultSet rs)
			throws SQLException {
		
		int schoolColumn = 0;
		try {
			schoolColumn = rs.findColumn("school");
		} catch (SQLException e) {
			schoolColumn = rs.findColumn("courseSchool");

		}
		
		Course course = new Course(rs.getString("courseCode"),
				rs.getString("title"), rs.getString("description"),
				rs.getString(schoolColumn), rs.getString("examDate"),
				rs.getString("examStartTime"), rs.getString("examEndTime"));

		Section section = new Section(course, rs.getString("sectionCode"),
				rs.getString("venue"), rs.getString("day"),
				rs.getString("startTime"), rs.getString("endTime"),
				rs.getString("instructor"), rs.getInt("size"));
		return section;
	}

}
